package com.pwt.repository;

import com.pwt.repository.base.BaseRepository;
import com.pwt.entity.Hierarchy;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * Hierarchy Repository层
 *
 * @author auto generated
 * @date 2021-10-08 11:30:48
 */
public interface HierarchyRepository extends BaseRepository<Hierarchy, String> {

    List<Hierarchy> findByAbilityLineId(String abilityLineId);

    @Query(nativeQuery = true,value = "select  * from  hierarchy ")
    List<Hierarchy> findAllNotInAbilityLineId();

    @Query(nativeQuery = true,value = "delete  from  hierarchy where ability_line_id=?1")
    @Modifying
    void deleteByAbilityLineId(String id);


    @Query(nativeQuery = true,value = "delete  from  hierarchy")
    @Modifying
    void deleteByAbilityLineIdIsNull();

    @Query(nativeQuery = true,value = "select  distinct number from hierarchy")
    List<Integer> queryNumber();

    @Query(nativeQuery = true,value = "SELECT h.id id,h.number number ,h.name name,h.pid pid from  hierarchy h\n" +
            "LEFT JOIN  what_line_hierarchy wh on h.id=wh.hierarchy_id\n" +
            "where wh.what_line_id=?1")
    List<Hierarchy> queryHierarchyListByWhatLAndAbilityLineId(String whatLineId);

}
